package com.primeton.sftp.excel;

import com.jcraft.jsch.SftpException;
import com.primeton.sftp.entity.InterfaceEntity;
import com.primeton.sftp.entity.ServiceEntity;
import com.primeton.sftp.util.SFTPUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.ResourceBundle;

public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
//    Logger logger = LogManager.getLogger("RollingRandomAccessFileLogger");

    private static HashMap<String,Double> getInterfaceSum() throws IOException {
        ResourceBundle resource = ResourceBundle.getBundle("path");
        String path = resource.getString("path.everyInterface");

        InterfaceEntity entity = new InterfaceEntity();
        HashMap<String,Double> map = new HashMap<String,Double>();
        //1、获取文件输入流
        InputStream inputStream = new FileInputStream(path);
        //2、获取Excel工作簿对象
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        //3、得到Excel工作表对象
        XSSFSheet sheetAt = workbook.getSheet("按分钟统计的每个接口调用量");
        //4、获取最后一行数据
        Row frontRow = sheetAt.getRow(0);
        Row lastRow = sheetAt.getRow(sheetAt.getLastRowNum());
        for (int i=1;i<frontRow.getLastCellNum();i++){
            String s = frontRow.getCell(i).getStringCellValue();
            double d = lastRow.getCell(i).getNumericCellValue();
            map.put(s,d);
        }
        inputStream.close();
        return map;
    }


//    public static void main(String[] args) throws IOException, SQLException, SftpException {
    public static void ProcessIn() throws IOException, SQLException{
        ResourceBundle resource = ResourceBundle.getBundle("path");
        String url = resource.getString("database.url");
        String id = resource.getString("database.id");
        String password = resource.getString("database.password");
        SFTPUtil sftpUtil = new SFTPUtil();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        HashMap<String,Double> map = getInterfaceSum();
        HashMap<String,Double>  map1 = new HashMap();
        Connection conn = DriverManager.getConnection(url, id, password);
//        System.out.println("连接成功");
        logger.info("连接成功");
        String sql = resource.getString("database.sql");

        pstm = conn.prepareStatement(sql);
        rs = pstm.executeQuery();
        while (rs.next()) {
            Double sum = Double.valueOf(rs.getString("sum"));
            String log_type = rs.getString("log_type");
            if(map.containsKey(log_type)) {
                map1.put(log_type,(map.get(log_type) - sum));
//                logger.info("接口名称:"+log_type);
//                logger.info("调用成功量：" + (map.get(log_type) - sum));
            }
        }
        InterfaceEntity entity = new InterfaceEntity();
//        ExcelOut(entity,map1,map);
        TxtOut(entity,map1,map);
        sftpUtil.uploadExl();

//        in.close();
    }

    private static void ExcelOut(InterfaceEntity entity, HashMap<String,Double> map,HashMap<String,Double> sumMap) throws IOException {
        ResourceBundle resource = ResourceBundle.getBundle("path");
        String path = resource.getString("path.out");
        String monthPath = resource.getString("path.monthout");

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        String currentTime = df.format(new Date());// new Date()为获取当前系统时间
        String s1 = currentTime.split(" ")[0]; // 2022-02-23
        path = path+"-"+s1.replaceAll("-","")+".xlsx";
        int currentMonth = Integer.valueOf(s1.substring(s1.indexOf("-")+1,s1.lastIndexOf("-"))); //2
        monthPath = monthPath+"-"+currentMonth+".xlsx";
        //1、获取文件输入流
//        InputStream inputStream = new FileInputStream(path);
        InputStream monthInputStream = new FileInputStream(monthPath);
        //2、获取Excel工作簿对象
        XSSFWorkbook workbook = new XSSFWorkbook(); // 生成excel
//        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
//        XSSFWorkbook monthWorkBook = new XSSFWorkbook();// 生成excel
        XSSFWorkbook monthWorkBook = new XSSFWorkbook(monthInputStream);
        XSSFCell writeCell;
        XSSFCell monthWriteCell;
        XSSFCell sumCell;
        XSSFCell monthSumCell;
        //3、得到Excel工作表对象
        XSSFSheet sheetAt = workbook.createSheet("测试模板");
//        XSSFSheet sheetAt = workbook.getSheet("测试模板");
//        XSSFSheet monthSheetAt = monthWorkBook.createSheet("测试模板");
        XSSFSheet monthSheetAt = monthWorkBook.getSheet("测试模板");
        monthInputStream.close();
//        inputStream.close();
//        int mid = 0;
        int mid = 42; // 记录的接口数量
        ExcelOutUtil.firstRow(sheetAt); // 日传列标题行
        ExcelOutUtil.firstRow(monthSheetAt); // 月传列标题行
        ExcelOutUtil.createRow(sheetAt,monthSheetAt); // 写入除接口量以外字段
        int monthRowNum = 2; // 2号 后面改成每天的日期 仅测试用
        for(int i=1;i<mid+1;i++){
            if(entity.map.containsKey(sheetAt.getRow(i).getCell(4).getStringCellValue())){
                String s = entity.map.get(sheetAt.getRow(i).getCell(4).getStringCellValue());
//                System.out.println(s);
                logger.info(s);
//                System.out.println(map.get(s));
                logger.info(String.valueOf(map.get(s)));

                sumCell = sheetAt.getRow(i).createCell(13);

                    monthSumCell = monthSheetAt.getRow(42 * (monthRowNum - 1) + i).createCell(13);
                    monthWriteCell = monthSheetAt.getRow(42 * (monthRowNum - 1) + i).createCell(14);

                writeCell = sheetAt.getRow(i).createCell(14);


                if(map.get(s)!=null) {
                    writeCell.setCellValue(map.get(s));
                    monthWriteCell.setCellValue(map.get(s));
                    sumCell.setCellValue(sumMap.get(s));
                    monthSumCell.setCellValue(sumMap.get(s));
                }
                else {
                    writeCell.setCellValue(0);
                    monthWriteCell.setCellValue(0);
                    sumCell.setCellValue(0);
                    monthSumCell.setCellValue(0);
                }
            }
        }
        FileOutputStream outputStream = new FileOutputStream(path);
        FileOutputStream monthOutputStream = new FileOutputStream(monthPath);
        workbook.write(outputStream);
        monthWorkBook.write(monthOutputStream);
        monthOutputStream.flush();
        monthOutputStream.close();
        outputStream.flush();
        outputStream.close();

    }

    private static void TxtOut(InterfaceEntity entity, HashMap<String,Double> map,HashMap<String,Double> sumMap) {
        logger.info("日传及月传-txt-输出");
        ResourceBundle resource = ResourceBundle.getBundle("path");
        String path = resource.getString("path.out");
        String monthPath = resource.getString("path.monthout");
        ServiceEntity serviceEntity = new ServiceEntity();

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        String currentTime = df.format(new Date());// new Date()为获取当前系统时间
        String s1 = currentTime.split(" ")[0]; // 2022-02-23
        String s2 = s1.split("-")[0]+s1.split("-")[1];
        //新业务逻辑
        //D_006_F10004_006_68903_20220223_00.txt

        String filepath = path+s1.replaceAll("-","")+"_00.txt";
        String chkpath = path+s1.replaceAll("-","")+"_00.chk";
        String monthTxtPath = monthPath+s2+"_00.txt";
        String monthChkPath = monthPath+s2+"_00.chk";
        File file = new File(filepath);
        File chkFile = new File(chkpath);
        File monthTxtFile = new File(monthTxtPath);
        File monthChkFile = new File(monthChkPath);
        try {
            if (!monthTxtFile.exists())
                monthTxtFile.createNewFile();
            if (!monthChkFile.exists())
                monthChkFile.createNewFile();
            file.createNewFile();
            chkFile.createNewFile();
            BufferedWriter writer = new BufferedWriter(new FileWriter(filepath, true));
            BufferedWriter monthWriter = new BufferedWriter(new FileWriter(monthTxtFile, true));
            BufferedWriter chkWriter = new BufferedWriter(new FileWriter(chkpath, true));
            BufferedWriter monthChkWriter = new BufferedWriter(new FileWriter(monthChkFile, true));
            for (int i = 0; i < 42; i++) {
                StringBuffer buffer = new StringBuffer(s1.replaceAll("-", ""));
                buffer.append("€€无€€M域集中化系统建设支撑€€A100600080€€集中化流程引擎能力€€API类€€无€€");
                buffer.append(ServiceEntity.list.get(i)).append("€€");
                buffer.append("OSB_BP_WFP_HQ_000" + (i + 38)).append("€€");
                buffer.append("集中化流程平台€€A100600080€€M域集中化系统€€无€€M域集中化系统€€无€€内部€€");
                String interName = InterfaceEntity.map.get(ServiceEntity.list.get(i));
                if ((map.get(interName)) != null) {
                    buffer.append(sumMap.get(interName)).append("€€");
                    buffer.append(map.get(interName)).append("€€");
                } else {
                    buffer.append(0).append("€€");
                    buffer.append(0).append("€€");
                }
                buffer.append("无€€无");
                writer.write(buffer.toString() + "\r\n");
                chkWriter.write(buffer.toString() + "\r\n");
                monthWriter.write(buffer.toString() + "\r\n");
                monthChkWriter.write(buffer.toString() + "\r\n");
            }
            writer.flush();
            writer.close();
            chkWriter.flush();
            chkWriter.close();
            monthWriter.flush();
            monthWriter.close();
            monthChkWriter.flush();
            monthChkWriter.close();
        } catch (IOException e) {
            logger.error(e.toString());
        }
    }

}
